package first.daodto;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import org.h2.jdbcx.JdbcConnectionPool;

import first.Library;
import first.liter.*;
import first.users.BiblioUser.UserType;
import first.liter.Literature.LITERATURE;
import first.nets.ReaderNetwork;
import first.users.Admin;
import first.users.BiblioUser;
import first.users.Librarian;
import first.users.Reader;

public class DaoImpl implements LiteratureDao, LibraryDao, NetworkDao,UserDao{
	
	/*
	 * 
	 */
	private JdbcConnectionPool cp;


//===========================Literature======================================================
	/*
	 * Method that finds all Literature in database 
	 */
	public List<Literature> findAllLiteraturesSimple() throws SQLException 
	{
		
		List<Literature> litt;
		
		Connection conn=null;
		
		Statement myStat = null;
	    try {
	         conn = getConnection(); 

	         myStat = conn.createStatement();
	         ResultSet resultSet = myStat.executeQuery("SELECT * FROM BK_JRN");
	         /*
	         int colCount = resultSet.getMetaData().getColumnCount();
	         for (int col = 1; col <= colCount; col++) {
	            System.out.println("col: " + col + " " + resultSet.getMetaData().getColumnName(col));
	         }
	          */
	         litt = new ArrayList<Literature>();

	         while (resultSet.next()) 
	         {
	            	
	        	 BaseLanguage bas1=AbstractLanguage.getLanguage(resultSet.getString("LANG"));
	            	
	        	 Literature book;
	        	 LITERATURE lt=LITERATURE.valueOf(resultSet.getString("TYPE").toUpperCase());
	        	 Boolean df;
	        	 switch (lt)
	        	 {		
	        		case BOOK: 
	        			litt=setBookORJournal(litt, resultSet, bas1,true);	 
		        		break;
	        		case JOURNAL:
	        			litt=setBookORJournal(litt, resultSet, bas1,false);
	        			break;
	        			default:{}
	        	 }
	            	
	         }
	    } 
	    finally 
	    {
	    	silentClose(myStat);
	    	silentClose(conn);
	    }

		return litt;
	}

private List<Literature> setBookORJournal(List<Literature> litt, ResultSet resultSet,
		BaseLanguage bas1,Boolean T) throws SQLException {
	Literature book=null;
	Boolean df;
	if (T==true)
	{
		book=bas1.createBooks(resultSet.getString("BKJRN_NAME"));
	}
	else
	{
		book=bas1.createJournals(resultSet.getString("BKJRN_NAME"));
	}
	book.setAmountOfPages(resultSet.getInt("AM_OF_PGS"));
	
	df=null;
	if (resultSet.getInt("AVAILABLE")==1)
	{
		df=true;
	}
	else
		if (resultSet.getInt("AVAILABLE")==0)
		{
			df=false;
		}
	
	book.setAvailabilityOfLit(df);
	book.setDesc(resultSet.getString("DESCR"));
	book.setEditNumLit(resultSet.getInt("EDITNUM"));
	book.setIdLit(resultSet.getInt("ID_BK_JRN"));
	litt.add(book);
	
	return litt;
}
	
	/*
	 * Creates row in table BK_JRN with information about Literature lit
	 * @see first.daodto.LiteratureDao#createLiterature(first.liter.Literature)
	 */
	public int createLiterature(Literature lit) throws SQLException {
		Connection conn=null;
		PreparedStatement prepStat=null;
		try
		{		
			conn=getConnection();
			prepStat=conn.prepareStatement("INSERT INTO BK_JRN( BKJRN_NAME , AUTHOR , AVAILABLE , LANG , TYPE , AM_OF_PGS, DESCR ,EDITNUM) VALUES (?,?,?,?,?,?,?,?)");
			
			prepStat.setString(1, lit.getNameLit());
			prepStat.setString(2, lit.getAuthor());
			Integer i=null;
			if (lit.getAvailabilityOfLit()==true) 
			{
				i=1;
			}else{
				if (lit.getAvailabilityOfLit()==false) i=0; 
			}
			prepStat.setInt(3, i);
			prepStat.setString(4, lit.getLanguageLit());
			prepStat.setString(5, lit.getTypeOfLiterature());
			prepStat.setInt(6, lit.getAmountOfPages());
			prepStat.setString(7, lit.getDesc());
			prepStat.setInt(8, lit.getEditNumLit());
			
			return prepStat.executeUpdate();
		}
		finally {
			silentClose(prepStat);
			silentClose(conn);
		}	
	}
	
	/*
	 * Deletes one row from table BK_JRN of such kind Literature lit
	 * @see first.daodto.LiteratureDao#deleteOneLiterature(first.liter.Literature)
	 */
	public int deleteOneLiterature(Literature lit) throws SQLException {
		Connection conn=null;
		
		PreparedStatement prepStat=null;
		
		try
		{
			conn=getConnection();
			
			prepStat=conn.prepareStatement("DELETE FROM BK_JRN WHERE  AUTHOR = ? AND BKJRN_NAME =? " +
					"AND EDITNUM =? AND ID_BK_JRN = (SeLECT MIN(ID_BK_JRN) FroM BK_JRN WHERE AUTHOR =? AND BKJRN_NAME =? AND EDITNUM =?)");
			
			prepStat.setString(1, lit.getAuthor());
			prepStat.setString(2, lit.getNameLit());
			prepStat.setInt(3, lit.getEditNumLit());
			prepStat.setString(4, lit.getAuthor());
			prepStat.setString(5, lit.getNameLit());
			prepStat.setInt(6, lit.getEditNumLit());
			
			return prepStat.executeUpdate();
		}
		finally
		{
			silentClose(prepStat);
			silentClose(conn);
		}
		
	}
	
	/*
	 * Deletes one row from table BK_JRN of such kind Literature lit
	 * @see first.daodto.LiteratureDao#deleteAllLiterature(first.liter.Literature)
	 */
	public int deleteAllLiteratures(Literature lit) throws SQLException {
		Connection conn=null;
		
		PreparedStatement prepStat=null;
		
		try
		{
			conn=getConnection();
			
			prepStat=conn.prepareStatement("DELETE FROM BK_JRN WHERE  AUTHOR = ? AND BKJRN_NAME =? AND EDITNUM =? ");
			
			prepStat.setString(1, lit.getAuthor());
			prepStat.setString(2, lit.getNameLit());
			prepStat.setInt(3, lit.getEditNumLit());
			
			return prepStat.executeUpdate();
		}
		finally
		{
			silentClose(prepStat);
			silentClose(conn);
		}
		
	}
	
//=================================================================================================================	
	
	

	
	
	
	
	
//======================================================POOL=======================================================	
    /**
     * call this once per app start/stop lifecycle
     */
    public void start() throws SQLException {
    	
        cp = createPool();
        cp.setMaxConnections(1);
    }

    /**
     * call this once per app start/stop lifecycle
     */
    public void stop() throws SQLException {
        cp.dispose();
        System.out.println("cp.getActiveConnections() = " + cp.getActiveConnections());
    }


    protected JdbcConnectionPool createPool() throws SQLException {
       /* Properties dbProps = new Properties();
        try {
            dbProps.load(DriverTest.class.getResourceAsStream("/epam_java/test/jdbc/db.properties"));
            Class.forName("org.h2.Driver");
        } catch (IOException e) {
            throw new SQLException(e);
        } catch (ClassNotFoundException e) {
            throw new SQLException(e);
        }

       return JdbcConnectionPool.create(dbProps.getProperty("url"), "sa", "");
       */
    	return JdbcConnectionPool.create("jdbc:h2:db/kozlov_biblio", "sa", "");
    }

//=================================================================================================================	
	
	
	
//=========================================CONNECTIONS=============================================================	
	 protected Connection getConnection() throws SQLException {
	        System.out.println("creating connection!");	        
	        //Properties dbProps = new Properties();
	        //dbProps.load(LiteratureDaoImpl.class.getResourceAsStream("/epam_java/test/jdbc/db.properties"));
	        
	        Connection connection = cp.getConnection();
	       // Connection connection =DriverManager.getConnection("jdbc:h2:/media/B32740CCC92117C1/Java/h2test/test", "sa", "");

	        return connection;
	    }
	
	
	public void silentClose(Connection conn) {
        System.out.println("destroying connection!");

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                //  ignored or reported to log4j with debug level
            }
        }
    }

    protected void silentClose(Statement myStat) {
        if (myStat != null) {
            try {
                myStat.close();
            } catch (SQLException e) {
                //  ignored or reported to log4j with debug level
            }
        }
    }
//================================================================================================================

//=============================================----Library----====================================================
  
//================================================================================================================

	@Override
	public int createLibrary(Library lib) throws SQLException 
	{
		Connection conn=null;
		PreparedStatement prepStat=null;
		try
		{		
			conn=getConnection();
			prepStat=conn.prepareStatement("INSERT INTO LIBRARY( LIB_NAME, LIB_DESCR) VALUES (?,?)");
			
			prepStat.setString(1, lib.getNameLibrary());
			prepStat.setString(2, lib.getDescrLibrary());
			
						
			return prepStat.executeUpdate();
		}
		finally {
			silentClose(prepStat);
			silentClose(conn);
		}	
	}

	@Override
	public int deleteAllLibraries(Library lib) throws SQLException 
	{
		Connection conn=null;
		
		PreparedStatement prepStat=null;
		
		try
		{
			conn=getConnection();
			
			prepStat=conn.prepareStatement("DELETE FROM LIBRARY WHERE  LIB_NAME = ? AND LIB_DESCR =?");
			
			prepStat.setString(1, lib.getNameLibrary());
			prepStat.setString(2, lib.getDescrLibrary());
			
			return prepStat.executeUpdate();
		}
		finally
		{
			silentClose(prepStat);
			silentClose(conn);
		}
		
	}

	@Override
	public int deleteOneLibrary(Library lib) throws SQLException {
		Connection conn=null;
		
		PreparedStatement prepStat=null;
		
		try
		{
			conn=getConnection();
			
			prepStat=conn.prepareStatement("DELETE FROM LIBRARY WHERE  LIB_NAME = ? AND LIB_DESCR =? AND  ID_LIB=? ");
			
			prepStat.setString(1, lib.getNameLibrary());
			prepStat.setString(2, lib.getDescrLibrary());
			prepStat.setInt(3, lib.getIdLibrary());
			
			return prepStat.executeUpdate();
		}
		finally
		{
			silentClose(prepStat);
			silentClose(conn);
		}
	}

	@Override
	public List<Library> findAllLibrariesSimple() throws SQLException {

		final List<Library> libb ;
		
		Connection conn=null;
		
		Statement myStat = null;
	    try 
	    {
	         conn = getConnection(); 

	         myStat = conn.createStatement();
	         ResultSet resultSet = myStat.executeQuery("SELECT * FROM LIBRARY");
	        
	         libb = new ArrayList<Library>();

	         while (resultSet.next()) 
	         {
	        	Library lib=new Library(resultSet.getString("LIB_NAME"));
	            	
	            lib.setDescrLibrary(resultSet.getString("LIB_DESCR"));
	        	lib.setIdLibrary(resultSet.getInt("ID_LIB"));
		        	
		        libb.add(lib);		
	         }
	    } 
	    finally 
	    {
	    	silentClose(myStat);
	    	silentClose(conn);
	    }

		return libb;
	}
//================================================================================================================

//=============================================----ReaderNetwork----====================================================
	  
//================================================================================================================

	@Override
	public int createReaderNetwork(ReaderNetwork net) throws SQLException {
		Connection conn=null;
		PreparedStatement prepStat=null;
		try
		{		
			conn=getConnection();
			prepStat=conn.prepareStatement("INSERT INTO NETWORK ( NET_NAME , NET_DESCR , ID_LIB ) VALUES (?,?,?)");
			
			prepStat.setString(1, net.getNameRNet());
			prepStat.setString(2, net.getDescrRNet());
			prepStat.setInt(3, net.getIdLibrary());
						
			return prepStat.executeUpdate();
		}
		finally {
			silentClose(prepStat);
			silentClose(conn);
		}	
	}

	@Override
	public int deleteAllReaderNetworks(ReaderNetwork net) throws SQLException {
		Connection conn=null;
		
		PreparedStatement prepStat=null;
		
		try
		{
			conn=getConnection();
			
			prepStat=conn.prepareStatement("DELETE FROM NETWORK WHERE  NET_NAME = ? AND NET_DESCR =? AND ID_LIB =?");
			
			prepStat.setString(1, net.getNameRNet());
			prepStat.setString(2, net.getDescrRNet());
			prepStat.setInt(3, net.getIdLibrary());;
			
			return prepStat.executeUpdate();
		}
		finally
		{
			silentClose(prepStat);
			silentClose(conn);
		}
	}

	@Override
	public int deleteOneReaderNetwork(ReaderNetwork net) throws SQLException {
		Connection conn=null;
		
		PreparedStatement prepStat=null;
		
		try
		{
			conn=getConnection();
			
			prepStat=conn.prepareStatement("DELETE FROM NETWORK WHERE  NET_NAME = ? AND NET_DESCR =? AND ID_LIB =? AND ID_NET =?");
			
			prepStat.setString(1, net.getNameRNet());
			prepStat.setString(2, net.getDescrRNet());
			prepStat.setInt(3, net.getIdLibrary());
			prepStat.setInt(4, net.getIdReaderNet());
			
			return prepStat.executeUpdate();
		}
		finally
		{
			silentClose(prepStat);
			silentClose(conn);
		}
	}

	@Override
	public List<ReaderNetwork> findAllReaderNetworksSimple()
			throws SQLException {
			final List<ReaderNetwork> nett ;
		
		Connection conn=null;
		
		Statement myStat = null;
	    try 
	    {
	         conn = getConnection(); 

	         myStat = conn.createStatement();
	         ResultSet resultSet = myStat.executeQuery("SELECT * FROM NETWORK");
	        
	         nett = new ArrayList<ReaderNetwork>();

	         while (resultSet.next()) 
	         {
	        	 ReaderNetwork net=new ReaderNetwork();
	            	
	        	net.setNameRNet(resultSet.getString("NET_NAME"));
	            net.setDescrRNet(resultSet.getString("NET_DESCR"));
	            net.setIdReaderNet(resultSet.getInt("ID_NET"));
	        	net.setIdLibrary(resultSet.getInt("ID_LIB"));
		        	
		        nett.add(net);		
	         }
	    } 
	    finally 
	    {
	    	silentClose(myStat);
	    	silentClose(conn);
	    }

		return nett;
	}

	
//================================================================================================================

//===============================================----USER----=====================================================
		  
//================================================================================================================
	
	@Override
	public int createUser(BiblioUser usr, Library lib) throws SQLException {
		Connection conn=null;
		PreparedStatement prepStat=null;
		ResultSet resultSet=null;
		
		try
		{		
			conn=getConnection();
			prepStat=conn.prepareStatement("INSERT INTO LIB_USER ( USER_NAME , USER_TYPE , LOGIN , PASSWORD ) VALUES (?,?,?,?)");
			
			prepStat.setString(1, usr.getName());
			prepStat.setString(2, usr.getUserType());
			prepStat.setString(3, usr.getUserLogin());
			prepStat.setInt(4, Integer.parseInt(usr.getPassword()));
						
			int q=prepStat.executeUpdate();
			
			
			prepStat=conn.prepareStatement("SELECT MAX(ID_USER) from LIB_USER");
		    		    
		    resultSet=prepStat.executeQuery();
		    
		    System.out.println("----------------=====----------------");
		        
		    Integer User_ID=null;
		    while (resultSet.next())
		    {
		        	User_ID=resultSet.getInt("MAX(ID_USER)");
		    }
		    
		    System.out.println("user id=============="+User_ID);
		    
		    prepStat=conn.prepareStatement("INSERT INTO LIB_AND_USR ( ID_USER , ID_LIB ) VALUES (?,?) ");
		    
		    prepStat.setInt(1, User_ID);
		    prepStat.setInt(2, lib.getIdLibrary());
		    
		    q=prepStat.executeUpdate();
		    
		    prepStat=conn.prepareStatement("INSERT INTO NET_AND_USR ( ID_NET , ID_USER ) VALUES ((SELECT ID_NET FROM NETWORK WHERE ID_LIB =?),?)");
		    prepStat.setInt(1, lib.getIdLibrary());
		    prepStat.setInt(2, User_ID);
		    
			q=prepStat.executeUpdate();
			//=====================		
		    prepStat=conn.prepareStatement("INSERT  INTO NETWORK ( NET_NAME , NET_DESCR , ID_LIB ) VALUES ('Friends',?,?)");
			prepStat.setString(1, "FriendsFromSomeLib");
			prepStat.setInt(2,lib.getIdLibrary());
			q=prepStat.executeUpdate();
			
			prepStat=conn.prepareStatement("INSERT INTO NET_AND_USR ( ID_NET , ID_USER ) VALUES ((SELECT MAX(ID_NET) FROM NETWORK),?)");
			prepStat.setInt(1,User_ID);
			
			    
		   return q=prepStat.executeUpdate();
		}
		finally {
			silentClose(prepStat);
			silentClose(conn);
		}	
	}

	@Override
	public int createUserInDefaultLibrary(BiblioUser usr) throws SQLException {
		Connection conn=null;
		PreparedStatement prepStat=null;
		
		try
		{		
			conn=getConnection();
			prepStat=conn.prepareStatement("INSERT INTO LIB_USER ( USER_NAME , USER_TYPE , LOGIN , PASSWORD ) VALUES (?,?,?,?)");
			
			prepStat.setString(1, usr.getName());
			prepStat.setString(2, usr.getUserType());
			prepStat.setString(3, usr.getUserLogin());
			prepStat.setInt(4, Integer.parseInt(usr.getPassword()));
						
			return prepStat.executeUpdate();		
		   
		}
		finally {
			silentClose(prepStat);
			silentClose(conn);
		}	
	}

	@Override
	public int deleteOneUser(BiblioUser usr) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int deleteUserFromLibrary(Library lib) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int deleteUserFromNetwork(ReaderNetwork net) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public List<BiblioUser> findAllLibraryUsers(Library lib) throws SQLException {

		final List<BiblioUser> usrs;
		
		Connection conn=null;
		
		Statement myStat = null;
		
		PreparedStatement prepStat=null;
		
		ResultSet resultSet=null;
		
	    try 
	    {
	    	conn = getConnection(); 

	        myStat = conn.createStatement();
	        
	        prepStat=conn.prepareStatement("SELECT MAX(ID_LIB) from LIBRARY where LIB_NAME =?");
	        
	        prepStat.setString(1, lib.getNameLibrary());
	        resultSet=prepStat.executeQuery();
	        System.out.println("============----------------");
	        
	        Integer LIB_ID=null;
	        while (resultSet.next())
	        {
	        System.out.println("ID _LIB ="+resultSet.getInt("MAX(ID_LIB)"));
	        LIB_ID=resultSet.getInt("MAX(ID_LIB)");
	        }
	        
	        prepStat=conn.prepareStatement("SELECT * FROM LIB_USER t1, LIB_AND_USR  t2 WHERE T1.ID_USER = t2.ID_USER and t2.ID_LIB = ?");
	        
	        prepStat.setInt(1, LIB_ID);
	        
	        resultSet=prepStat.executeQuery();
	          
//      ResultSet resultSet = myStat.executeQuery("SELECT * FROM LIB_USER t1, LIB_AND_USR  t2 WHERE T1.ID_USER = t2.ID_USER and t2.ID_LIB = 23");
	        
	    	usrs=new ArrayList<BiblioUser>();
	    
	    	Reader usr1=null;
	    	Librarian usr2=null;
	    	Admin usr3=null;
	    	
	    	while (resultSet.next()) 
		    {
	    		UserType my = UserType.valueOf(resultSet.getString("USER_TYPE").toUpperCase());
	    		switch (my)
	    		{		
	    		case READER: 
	    			usr1=new Reader(resultSet.getString("LOGIN"),resultSet.getString("USER_NAME"), String.valueOf(resultSet.getInt("PASSWORD")), resultSet.getInt("ID_USER"));
	    		break;
	    		case LIBRARIAN: 
	    			usr2=new Librarian(resultSet.getString("LOGIN"),resultSet.getString("USER_NAME"), String.valueOf(resultSet.getInt("PASSWORD")), resultSet.getInt("ID_USER"));
	    		break;
	    		case ADMIN: 
	    			usr3=Admin.getInstance(resultSet.getString("LOGIN"),resultSet.getString("USER_NAME"), String.valueOf(resultSet.getInt("PASSWORD")), resultSet.getInt("ID_USER"));
	   			break;
	    		default:{}// throw new EnumConstantNotPresentException(Signs.class, sign.name())
	    		}
	    		
	    		if (usr1!=null)
	    			usrs.add(usr1);	
	    		if (usr2!=null)
				    usrs.add(usr2);	
	    		if (usr3!=null)
				    usrs.add(usr3);	
		    }
	    	
	    } 
	    finally 
	    {
	    	silentClose(myStat);
	    	silentClose(conn);
	    }

		return usrs;
	}
////////////// исправить!!!
	@Override
	public List<BiblioUser> findAllNetworkUsers(ReaderNetwork net)
			throws SQLException {
	final List<BiblioUser> usrs;
		
		Connection conn=null;
		
		Statement myStat = null;
		
		PreparedStatement prepStat=null;
		
		ResultSet resultSet=null;
		
	    try 
	    {
	    	conn = getConnection(); 

	        myStat = conn.createStatement();
	        
	        prepStat=conn.prepareStatement("SELECT MAX(ID_LIB) from NETWORK where LIB_NAME =?");
	        
	        prepStat.setString(1, net.getNameRNet());
	        resultSet=prepStat.executeQuery();
	        System.out.println("============----------------");
	        
	        Integer LIB_ID=null;
	        while (resultSet.next())
	        {
	        System.out.println("ID _LIB ="+resultSet.getInt("MAX(ID_LIB)"));
	        LIB_ID=resultSet.getInt("MAX(ID_LIB)");
	        }
	        
	        prepStat=conn.prepareStatement("SELECT * FROM LIB_USER t1, LIB_AND_USR  t2 WHERE T1.ID_USER = t2.ID_USER and t2.ID_LIB = ?");
	        
	        prepStat.setInt(1, LIB_ID);
	        
	        resultSet=prepStat.executeQuery();
	          
//      ResultSet resultSet = myStat.executeQuery("SELECT * FROM LIB_USER t1, LIB_AND_USR  t2 WHERE T1.ID_USER = t2.ID_USER and t2.ID_LIB = 23");
	        
	    	usrs=new ArrayList<BiblioUser>();
	    
	    	Reader usr1=null;
	    	Librarian usr2=null;
	    	Admin usr3=null;
	    	
	    	while (resultSet.next()) 
		    {
	    		UserType my = UserType.valueOf(resultSet.getString("USER_TYPE").toUpperCase());
	    		switch (my)
	    		{		
	    		case READER: 
	    			usr1=new Reader(resultSet.getString("LOGIN"),resultSet.getString("USER_NAME"), String.valueOf(resultSet.getInt("PASSWORD")), resultSet.getInt("ID_USER"));
	    		break;
	    		case LIBRARIAN: 
	    			usr2=new Librarian(resultSet.getString("LOGIN"),resultSet.getString("USER_NAME"), String.valueOf(resultSet.getInt("PASSWORD")), resultSet.getInt("ID_USER"));
	    		break;
	    		case ADMIN: 
	    			usr3=Admin.getInstance(resultSet.getString("LOGIN"),resultSet.getString("USER_NAME"), String.valueOf(resultSet.getInt("PASSWORD")), resultSet.getInt("ID_USER"));
	   			break;
	    		default:{}// throw new EnumConstantNotPresentException(Signs.class, sign.name())
	    		}
	    		
	    		if (usr1!=null)
	    			usrs.add(usr1);	
	    		if (usr2!=null)
				    usrs.add(usr2);	
	    		if (usr3!=null)
				    usrs.add(usr3);	
		    }
	    	
	    } 
	    finally 
	    {
	    	silentClose(myStat);
	    	silentClose(conn);
	    }

		return usrs;
	}


}
